-- Tables
create table public.autonomies (id_autonomy int4 not null, autonomy_name varchar(30) not null, primary key (id_autonomy), unique (autonomy_name));
create table public.council_group_members (id_group int8 not null, id_council varchar(6) not null, primary key (id_group, id_council));
create table public.council_groups (id_group int8 not null, group_name varchar(40) not null, id_organization int4 not null, primary key (id_group), unique (group_name, id_organization));
create table public.councils (id_council varchar(6) not null, council_code varchar(4) not null, council_name varchar(100) not null, id_province varchar(2) not null, primary key (id_council));
create table public.imported_files (id_file int8 not null, correct_records int4 not null, warned_records int4 not null, import_date date not null, filename varchar(80) not null, total_records int4 not null, id_state int4 not null, first_date date null, last_date date null, id_organization int4 not null, primary key (id_file));
create table public.knowledge_areas (id_area int4 not null, area_code varchar(20) not null, area_description varchar(300) not null, id_organization int4 not null, primary key (id_area), unique (area_code, id_organization));
create table public.knowledge_path_units (id_path int4 not null, id_unit int8 not null, primary key (id_unit, id_path));
create table public.knowledge_paths (id_path int4 not null, path_code varchar(20) not null, path_description varchar(200) not null, id_area int4 not null, primary key (id_path), unique (path_code, id_area));
create table public.knowledge_units (id_unit int8 not null, unit_code varchar(20) not null, unit_name varchar(50) not null, unit_description varchar(1000) not null, id_organization int4 not null, primary key (id_unit), unique (unit_code, id_organization));
create table public.organization_provinces (id_province varchar(2) not null, id_organization int4 not null, primary key (id_organization, id_province));
create table public.organizations (id_organization int4 not null, organization_name varchar(50) not null, primary key (id_organization), unique (organization_name));
create table public.provinces (id_province varchar(2) not null, province_name varchar(30) not null, id_autonomy int4 not null, primary key (id_province), unique (province_name));
create table public.qualification_units (id_qualification int4 not null, id_unit int8 not null, primary key (id_unit, id_qualification));
create table public.qualifications (id_qualification int4 not null, qualification_code varchar(20) not null, qualification_description varchar(300) not null, id_organization int4 not null, primary key (id_qualification), unique (qualification_code, id_organization));
create table public.questions (id_question int8 not null, question_code varchar(20) not null, question_description varchar(255) not null, id_survey int4 not null, id_unit int8 not null, primary key (id_question), unique (question_code, id_survey));
create table public.survey_answer_headers (id_header int8 not null, entity_id varchar(9) not null, internet int4, creation_date date not null, import_date date not null, id_origin int4 not null, id_council varchar(6), id_survey int4 not null, id_action int4 not null, id_file int8 null, primary key (id_header));
create table public.survey_answer_headers_imported (id_header int8 not null, creation_date date, import_date date not null, id_action int4, action_code varchar(20), entity_id varchar(9), internet int4, id_council varchar(6), id_survey int4, survey_code varchar(20), id_status int4 not null, id_origin int4 not null, id_file int8 not null, primary key (id_header));
create table public.survey_unit_answers (id_header int8 not null, id_question int8 not null, training_preference int4 not null, covered bool not null, primary key (id_header, id_question));
create table public.survey_unit_answers_imported (id_header int8 not null, question_number int4 not null, question_code varchar(20) not null, id_question int8, id_status int4 not null, training_preference int4, primary key (id_header, question_number));
create table public.surveys (id_survey int4 not null, survey_code varchar(20) not null, survey_title varchar(60) not null, survey_description varchar(200) not null, creation_date date not null, start_date date not null, end_date date not null, archived_date date not null, id_survey_lifecycle int4 not null, id_organization int4 not null, primary key (id_survey), unique (survey_code, id_organization));
create table public.training_actions (id_action int4 not null, action_code varchar(20) not null, action_name varchar(150) not null, action_description varchar(800) not null, id_organization int4 not null, primary key (id_action), unique (action_code, id_organization));
create table public.training_actions_units (id_action int4 not null, id_unit int8 not null, primary key (id_unit, id_action));
create table public.users (id_user int4 not null, user_login varchar(20) not null, user_password varchar(60) not null, user_fullname varchar(50) not null, user_email varchar(50) not null, user_language varchar(8) not null, disable_date timestamp, id_role int4 not null, id_organization int4 not null, primary key (id_user), unique (user_login));

-- Enumeration tables
create table public.roles (id_role int4 not null, role_name varchar(30) not null, primary key (id_role), unique (role_name));
create table public.survey_lifecycles (id_survey_lifecycle int4 not null, lifecycle_name varchar(50) not null, primary key (id_survey_lifecycle));
create table public.training_preferences (id_training_preference int4 not null, preference_description varchar(200) not null, primary key (id_training_preference));
create table public.answer_origins(id_origin int4 not null, origin_name varchar(20) not null, primary key (id_origin));
create table public.imported_file_status(id_status int4 not null, status_name varchar(30) not null, primary key (id_status));
create table public.survey_header_validation(id_status int4 not null, status_name varchar(30) not null, primary key (id_status));
create table public.survey_answer_validation(id_status int4 not null, status_name varchar(30) not null, primary key (id_status));

-- Constraints
alter table public.council_group_members add constraint FK_COUNCIL_GROUP_GROUP foreign key (id_group) references public.council_groups;
alter table public.council_group_members add constraint FK_COUNCIL_GROUP_COUNCIL foreign key (id_council) references public.councils;
alter table public.council_groups add constraint FK_COUNCIL_GROUP_ORGANIZATION foreign key (id_organization) references public.organizations;
alter table public.councils add constraint FK_COUNCIL_PROVINCE foreign key (id_province) references public.provinces;
alter table public.imported_files add constraint FK_IMPORTED_FILE_ORGANIZATION foreign key (id_organization) references public.organizations;
alter table public.knowledge_areas add constraint FK_KNOWLEDGE_AREA_ORGANIZATION foreign key (id_organization) references public.organizations;
alter table public.knowledge_path_units add constraint FK_KNOWLEDGE_PATH_PATH foreign key (id_path) references public.knowledge_paths;
alter table public.knowledge_path_units add constraint FK_KNOWLEDGE_PATH_UNIT foreign key (id_unit) references public.knowledge_units;
alter table public.knowledge_paths add constraint FK_KNOWLEDGE_PATH_AREA foreign key (id_area) references public.knowledge_areas;
alter table public.knowledge_units add constraint FK_KNOWLEDGE_UNIT_ORGANIZATION foreign key (id_organization) references public.organizations;
alter table public.organization_provinces add constraint FK_ORGANIZATION_PROVINCE_ORGANIZATION foreign key (id_organization) references public.organizations;
alter table public.organization_provinces add constraint FK_ORGANIZATION_PROVINCE_PROVINCE foreign key (id_province) references public.provinces;
alter table public.provinces add constraint FK_PROVINCE_AUTONOMY foreign key (id_autonomy) references public.autonomies;
alter table public.qualification_units add constraint FK_QUALIFICATION_UNIT_UNIT foreign key (id_unit) references public.knowledge_units;
alter table public.qualification_units add constraint FK_QUALIFICATION_UNIT_QUALIFICATION foreign key (id_qualification) references public.qualifications;
alter table public.qualifications add constraint FK_QUALIFICATION_ORGANIZATION foreign key (id_organization) references public.organizations;
alter table public.questions add constraint FK_QUESTION_UNIT foreign key (id_unit) references public.knowledge_units;
alter table public.questions add constraint FK_QUESTION_SURVEY foreign key (id_survey) references public.surveys;
alter table public.survey_answer_headers add constraint FK_SURVEY_ANSWER_HEADER_ACTIVITY foreign key (id_action) references public.training_actions;
alter table public.survey_answer_headers add constraint FK_SURVEY_ANSWER_HEADER_COUNCIL foreign key (id_council) references public.councils;
alter table public.survey_answer_headers add constraint FK_SURVEY_ANSWER_HEADER_SURVEY foreign key (id_survey) references public.surveys;
alter table public.survey_answer_headers add constraint FK_SURVEY_ANSWER_HEADERS_FILE foreign key (id_file) references public.imported_files;
alter table public.survey_answer_headers_imported add constraint FK_SURVEY_ANSWER_HEADERS_IMPORTED_FILE foreign key (id_file) references public.imported_files;
alter table public.survey_unit_answers add constraint FK_SURVEY_UNIT_ANSWER_QUESTION foreign key (id_question) references public.questions;
alter table public.survey_unit_answers add constraint FK_SURVEY_UNIT_ANSWER_HEADER foreign key (id_header) references public.survey_answer_headers;
alter table public.survey_unit_answers_imported add constraint FK_SURVEY_UNIT_ANSWERS_IMPORTED_HEADER foreign key (id_header) references public.survey_answer_headers_imported;
alter table public.surveys add constraint FK_SURVEY_ORGANIZATION foreign key (id_organization) references public.organizations;
alter table public.training_actions add constraint FK_TRAINING_ACTIVITY_ORGANIZATION foreign key (id_organization) references public.organizations;
alter table public.training_actions_units add constraint FK_TRAINING_ACTIVITY_ACTIVITY foreign key (id_action) references public.training_actions;
alter table public.training_actions_units add constraint FK_TRAINING_ACTIVITY_UNIT foreign key (id_unit) references public.knowledge_units;
alter table public.users add constraint FK_USER_ORGANIZATION foreign key (id_organization) references public.organizations;

-- TODO: missing constaints in load model: survey_answer_headers_imported --> (councils, surveys, training_actions)
-- TODO: missing constaints in load model: survey_unit_answers_imported --> (questions)

-- Enumeration constraints
alter table public.surveys add constraint FK_SURVEY_LIFECYCLE foreign key(id_survey_lifecycle) references public.survey_lifecycles;
alter table public.survey_answer_headers add constraint FK_SURVEY_ANSWER_HEADER_ORIGIN foreign key(id_origin) references public.answer_origins;
alter table public.survey_unit_answers add constraint FK_SURVEY_UNIT_ANSWER_TRAINING foreign key (training_preference) references public.training_preferences;
alter table public.users add constraint FK_USER_ROLE foreign key (id_role) references public.roles;
alter table public.imported_files add constraint FK_IMPORTED_FILE_STATUS foreign key (id_state) references public.imported_file_status;
alter table public.survey_answer_headers_imported add constraint FK_IMPORTED_HEADER_STATUS foreign key (id_status) references public.survey_header_validation;
alter table public.survey_answer_headers_imported add constraint FK_IMPORTED_HEADER_ORIGIN foreign key (id_origin) references public.answer_origins;
alter table public.survey_unit_answers_imported add constraint FK_SURVEY_IMPORTED_UNIT_STATUS foreign key (id_status) references public.survey_answer_validation;
alter table public.survey_unit_answers_imported add constraint FK_SURVEY_IMPORTED_UNIT_PREFERENCE foreign key (training_preference) references public.training_preferences;


-- Sequences
create sequence public.council_groups_seq;
create sequence public.imported_files_seq;
create sequence public.knowledge_areas_seq;
create sequence public.knowledge_paths_seq;
create sequence public.knowledge_units_seq;
create sequence public.organizations_seq;
create sequence public.qualifications_seq;
create sequence public.questions_seq;
create sequence public.survey_answer_headers_imported_seq;
create sequence public.survey_answer_headers_seq;
create sequence public.surveys_seq;
create sequence public.training_actions_seq;
create sequence public.users_seq;

--
--     Data
--

-- Roles
insert into ROLES(ID_ROLE, ROLE_NAME) values (1, 'role.administrator');
insert into ROLES(ID_ROLE, ROLE_NAME) values (2, 'role.organization.admin');
insert into ROLES(ID_ROLE, ROLE_NAME) values (3, 'role.organization.designer');
insert into ROLES(ID_ROLE, ROLE_NAME) values (4, 'role.organization.user');


-- Survey lifecycles
insert into SURVEY_LIFECYCLES(ID_SURVEY_LIFECYCLE, LIFECYCLE_NAME) values(1, 'survey.design');
insert into SURVEY_LIFECYCLES(ID_SURVEY_LIFECYCLE, LIFECYCLE_NAME) values(2, 'survey.production');
insert into SURVEY_LIFECYCLES(ID_SURVEY_LIFECYCLE, LIFECYCLE_NAME) values(3, 'survey.closed');
insert into SURVEY_LIFECYCLES(ID_SURVEY_LIFECYCLE, LIFECYCLE_NAME) values(4, 'survey.archived');

-- Training preferences
insert into TRAINING_PREFERENCES(ID_TRAINING_PREFERENCE, PREFERENCE_DESCRIPTION) values(0, 'training.unknown');
insert into TRAINING_PREFERENCES(ID_TRAINING_PREFERENCE, PREFERENCE_DESCRIPTION) values(1, 'training.presential');
insert into TRAINING_PREFERENCES(ID_TRAINING_PREFERENCE, PREFERENCE_DESCRIPTION) values(2, 'training.remote');
insert into TRAINING_PREFERENCES(ID_TRAINING_PREFERENCE, PREFERENCE_DESCRIPTION) values(3, 'training.mixed');

-- Survey answer origins
insert into ANSWER_ORIGINS(ID_ORIGIN, ORIGIN_NAME) values(1, 'answer.origin.load');
insert into ANSWER_ORIGINS(ID_ORIGIN, ORIGIN_NAME) values(2, 'answer.origin.web');

-- Autonomies
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (0,'No tiene');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (1,'Andalucía');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (2,'Aragón');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (3,'Principado de Asturias');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (4,'Islas Baleares');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (5,'País Vasco');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (6,'Canarias');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (7,'Cantabria');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (8,'Castilla-La Mancha');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (9,'Castilla y León');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (10,'Cataluña');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (11,'Extremadura');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (12,'Galicia');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (13,'Comunidad de Madrid');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (14,'Región de Murcia');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (15,'Comunidad Foral de Navarra');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (16,'La Rioja');
insert into AUTONOMIES(ID_AUTONOMY, AUTONOMY_NAME) values (17,'Comunidad Valenciana');

-- Provinces
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('01', 'Álava', 5);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('02', 'Albacete', 8);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('03', 'Alicante/Alacant', 17);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('04', 'Almería', 1);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('33', 'Asturias', 3);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('05', 'Ávila', 9);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('06', 'Badajoz', 11);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('07', 'Balears (Illes)', 4);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('08', 'Barcelona', 10);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('09', 'Burgos', 9);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('10', 'Cáceres', 11);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('11', 'Cádiz', 1);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('39', 'Cantabria', 7);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('12', 'Castellón/Castelló', 17);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('51', 'Ceuta', 0);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('13', 'Ciudad Real', 8);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('14', 'Córdoba', 1);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('15', 'Coruña (A)', 12);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('16', 'Cuenca', 8);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('17', 'Girona', 10);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('18', 'Granada', 1);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('19', 'Guadalajara', 8);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('20', 'Guipúzcoa', 5);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('21', 'Huelva', 1);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('22', 'Huesca', 2);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('23', 'Jaén', 1);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('24', 'León', 9);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('27', 'Lugo', 12);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('25', 'Lleida', 10);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('28', 'Madrid', 13);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('29', 'Málaga', 1);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('52', 'Melilla', 0);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('30', 'Murcia', 14);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('31', 'Navarra', 15);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('32', 'Ourense', 12);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('34', 'Palencia', 9);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('35', 'Palmas (Las)', 6);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('36', 'Pontevedra', 12);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('26', 'Rioja (La)', 16);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('37', 'Salamanca', 9);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('38', 'Santa Cruz de Tenerife', 6);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('40', 'Segovia', 9);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('41', 'Sevilla', 1);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('42', 'Soria', 9);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('43', 'Tarragona', 10);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('44', 'Teruel', 2);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('45', 'Toledo', 8);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('46', 'Valencia/València', 17);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('47', 'Valladolid', 9);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('48', 'Vizcaya', 5);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('49', 'Zamora', 9);
insert into PROVINCES(ID_PROVINCE, PROVINCE_NAME, ID_AUTONOMY) values ('50', 'Zaragoza', 2);


-- Imported file status
insert into imported_file_status(id_status, status_name) values(1, 'file.status.uploaded');
insert into imported_file_status(id_status, status_name) values(2, 'file.status.validated');
insert into imported_file_status(id_status, status_name) values(3, 'file.status.discarded');
insert into imported_file_status(id_status, status_name) values(4, 'file.status.imported');

-- Survey answer validation
insert into survey_answer_validation(id_status, status_name) values(0, 'survey.answer.invalid');
insert into survey_answer_validation(id_status, status_name) values(1, 'survey.answer.valid');
insert into survey_answer_validation(id_status, status_name) values(2, 'survey.answer.duplicated');

-- Survey header validation
insert into survey_header_validation(id_status, status_name) values(0, 'survey.header.invalid');
insert into survey_header_validation(id_status, status_name) values(1, 'survey.header.valid');
insert into survey_header_validation(id_status, status_name) values(2, 'survey.header.duplicated');
insert into survey_header_validation(id_status, status_name) values(3, 'survey.header.expired');

--
--      Modelo analisis (agregaciones y otros)
--

--
--  Checks if a character is a number
--
create or replace function is_num( ch char(1) ) returns bool as $$
    declare
        nums constant char(10) := '0123456789';
    begin
        return position(coalesce(ch, '') in nums) > 0;
    end;
$$ language plpgsql;



--
-- NIF checksum
--
--	NIF: ([0-9]{1,8})([A-Z])  --> 8 digits + Letter (CRC)
--  NIE: X([0-9]{1,7})([A-Z]) --> X + 7 digits + Letter (CRC)
--
create or replace function checksum_NIF( NIF numeric(8,0) ) returns char(1) as $$
    declare
        NIF_LETTERS constant char(23) := 'TRWAGMYFPDXBNJZSQVHLCKE';
    begin
        return substr(NIF_LETTERS, (NIF % 23)::integer+1, 1);
    end;
$$ language plpgsql;


--
--		Check NIF / NIE
--
create or replace function is_nif( NIF char(9) ) returns bool as $$
    declare
				theNIF char(9);
        crc char(1);
        fchar char(1);
        calc char(1);
    begin
				theNIF := coalesce(NIF, '');
				if length(theNIF) <> 9 then
					return false;
				end if;
				if not theNIF ~ '([0-9]|X|Y|Z)[0-9]{7}[A-Z]' then
					return false;
				end if;
        crc := substr(theNIF, length(theNIF), 1);
        fchar :=  substr(theNIF, 1, 1);
        if is_num(fchar) then
            calc := checksum_NIF( substr(theNIF, 1, length(theNIF)-1)::numeric(8) );
        else
						fchar :=  case fchar when 'X' then '0' when 'Y' then '1' when 'Z' then '2' end;
            calc := checksum_NIF( (fchar || substr(theNIF, 2, length(theNIF)-2))::numeric(8) );
        end if;
        if calc = crc then
            return true;
        else
            return false;
        end if;
    end;
$$ language plpgsql;


--
-- CIF checksum
--
--	CIF: [ABCDEFGHKLMNPQS]([0-9]{7})([A-Z0-9])  --> Letter + 7 digits + Digit or Letter (CRC)
--
--
create or replace function checksum_CIF( CIF numeric(7,0) ) returns char(1) as $$
    declare
        CIF_ARRAY constant char(10) := '0246813579';
        CIFstring char(7);
        len integer;
        digit integer;
        accum integer;
    begin
        CIFString := CIF::char(7);
        len := length(CIFstring);
        accum := 0;
        for i in 1..len loop
            digit := substr(CIFstring, i, 1);
            if ((i%2) = 0) then
                accum := accum + digit;
            else
                accum := accum + (substr(CIF_ARRAY, digit+1, 1))::integer;
            end if;
        end loop;
        accum := 10 - (accum % 10);
        if accum = 10 then
            accum := 0;
        end if;
        return accum;
    end;
$$ language plpgsql;


--
--		Check CIF
--
create or replace function is_cif( CIF char(9) ) returns bool as $$
	declare
		CIF_LETTERS constant char(23) := 'JABCDEFGHI';
        theCIF char(9);
		crc char(1);
		fchar char(1);
		calc char(1);
	begin
		theCIF := coalesce(CIF, '');
		if length(theCIF) <> 9 then
			return false;
		end if;
		if not theCIF ~ '[ABCDEFGHKLMNPQS]([0-9]{7})([A-Z0-9])' then
			return false;
		end if;
		crc := substr(theCIF, length(theCIF), 1);
		fchar :=  substr(theCIF, 1, 1);
		calc := checksum_CIF( substr(theCIF, 2, length(theCIF)-2)::numeric(7) );
		if not is_num(crc) then
			calc := substr(CIF_LETTERS, calc::integer + 1, 1);
		end if;
		if calc = crc then
			return true;
		else
			return false;
		end if;
	end;
$$ language plpgsql;


--
--	Generate random NIF
--
create or replace function generate_NIF() returns char(9) as $$
    declare
        res char(9) := '';
        NUMS constant char(10) := '0123456789';
    begin
        for i in 1..8 loop
            res := res || substr(NUMS, (random()*9)::integer+1, 1);
        end loop;
        res := res || checksum_NIF(res::numeric(8,0));
        return res;
    end;
$$ language plpgsql;


--
-- 	Generate id from date
--
create or replace function date_to_id(thedate date) returns char(8) as $$
    declare
        res char(8) := '';
    begin
        res := extract(year from thedate)::char(4) || lpad(extract(month from thedate)::char(2), 2, '0') || lpad(extract(day from thedate)::char(2), 2, '0');
        return res;
    end;
$$ language plpgsql;


create or replace function date_to_id(thedate timestamp) returns char(8) as $$
    declare
        res char(8) := '';
    begin
        res := extract(year from thedate)::char(4) || lpad(extract(month from thedate)::char(2), 2, '0') || lpad(extract(day from thedate)::char(2), 2, '0');
        return res;
    end;
$$ language plpgsql;

create or replace function date_to_id(thedate timestamp with time zone) returns char(8) as $$
    declare
        res char(8) := '';
    begin
        res := extract(year from thedate)::char(4) || lpad(extract(month from thedate)::char(2), 2, '0') || lpad(extract(day from thedate)::char(2), 2, '0');
        return res;
    end;
$$ language plpgsql;

